﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Text;
using System.IO;
//Clone
using System.Data.Common;
using System.Data;
using Npgsql;

namespace org.maquech.CEeL.DB {
    public sealed class DBConsoleInstaller {
        /// <summary>
        /// Database schema filename.
        /// </summary>
        static string schemaFile;

        /// <summary>
        /// List of files to load to the database.
        /// </summary>
        static string [ ] filesToLoad;

        /// <summary>
        /// Directory containing scripts to load to the database.
        /// </summary>
        static string filesDir;

        /// <summary>
        /// Extension of files to load to database.
        /// </summary>
        static string filesToLoadExt;

        /// <summary>
        /// DBConsoleInstaller main function.
        /// </summary>
        /// <param name="args">An array of arguments</param>
        static void Main ( string [ ] args ) {
            if ( LoadSettings( ) )
                SetupDB( );
            Console.WriteLine( "Press any key..." );
            Console.ReadLine( );
        }

        /// <summary>
        /// Loads all settings from App.config file.
        /// </summary>
        /// <returns>True if settings were loaded, false otherwise</returns>
        private static bool LoadSettings ( ) {
            bool loaded = false;
            try {
                Console.Write( "Loading settings ... " );
                schemaFile = ConfigurationManager.AppSettings [ "SchemaFile" ].ToString( );
                filesDir = ConfigurationManager.AppSettings [ "FilesDir" ].ToString( );
                filesToLoadExt = ConfigurationManager.AppSettings [ "FilesToLoadExt" ].ToString( );
                filesToLoad = Directory.GetFiles( filesDir, "*" + filesToLoadExt );
                loaded = true;
                Console.WriteLine( "[ Done ]" );
            }
            catch ( Exception e ) {
                Console.WriteLine( "Hubo un error al cargar la configuración: " + e.Message );
            }
            return loaded;
        }

        /// <summary>
        /// Sets up the database.
        /// </summary>
        /// <returns>True if the database and the user were successfully installed.</returns>
        private static bool SetupDB ( ) {
            bool existsDB = false;
            bool existsDBUsr = false;
            bool setupOk = false;
            //TODO: Hacer esta parte dinámica
            Console.WriteLine( "Setting up the database ... " );
            string pgConStr = ConfigurationManager.ConnectionStrings [ "postgres" ].ConnectionString;
            using ( NpgsqlConnection pgDB = new NpgsqlConnection( pgConStr ) ) {
                try {
                    if ( pgDB.State == ConnectionState.Closed )
                        pgDB.Open( );
                    string sqlCommand = "SELECT * FROM"
                            + "(SELECT (count(*) = 1) as ceel_usr_exists FROM pg_user WHERE usename = 'ceel') as u,"
                            + "(SELECT (count(*) = 1) as ceel_db_exists FROM pg_database WHERE datname = 'ceel') as d";
                    NpgsqlCommand cmd = new NpgsqlCommand( sqlCommand, pgDB );
                    using ( NpgsqlDataReader rdr = cmd.ExecuteReader( ) ) {
                        if ( rdr.Read( ) ) {
                            existsDB = ( bool ) rdr [ "ceel_db_exists" ];
                            existsDBUsr = ( bool ) rdr [ "ceel_usr_exists" ];
                        }
                        rdr.Close( );
                    }

                    //User creation
                    if ( !existsDBUsr ) {
                        Console.Write( "Database User doesn't exist. Creating ... " );
                        sqlCommand = "CREATE USER ceel WITH ENCRYPTED PASSWORD 'orejaCeel' NOCREATEDB NOCREATEUSER";
                        cmd = new NpgsqlCommand(sqlCommand, pgDB );
                        existsDBUsr = ( cmd.ExecuteNonQuery( ) < 0 );
                        if ( existsDBUsr )
                            Console.WriteLine( "[ Done ]" );
                    }

                    if ( existsDBUsr ) {
                        Console.WriteLine( "Database User found..." );
                        //If database already exists, we drop it
                        if ( existsDB ) {
                            Console.Write( "Dropping old database ... " );
                            sqlCommand = "DROP DATABASE ceel ";
                            cmd = new NpgsqlCommand( sqlCommand, pgDB );
                            cmd.Prepare( );
                            existsDB = !( cmd.ExecuteNonQuery( ) < 0 );
                            if ( !existsDB )
                                Console.WriteLine( "[ Done ]" );
                        }

                        //Database creation
                        if ( !existsDB ) {
                            Console.Write( "Creating new database ... " );
                            sqlCommand = "CREATE DATABASE ceel OWNER ceel ENCODING 'UTF8' ";
                            cmd = new NpgsqlCommand( sqlCommand, pgDB );
                            existsDB = ( cmd.ExecuteNonQuery( ) < 0 );
                            if ( existsDB )
                                Console.WriteLine( "[ Done ]" );
                        }
                    }
                    else {
                        Console.WriteLine( "No se logró crear o no existe el usuario de la base de datos." );
                        setupOk = false;
                    }
                }
                catch ( Exception e ) {
                    Console.WriteLine( "Hubo un error al determinar la existencia de la base de datos y el usuario: " + e.Message );
                    setupOk = false;
                }
                finally {
                    if ( pgDB != null )
                        pgDB.Close( );
                    //If database and user exists, we load the schema and data into it.
                    if ( existsDBUsr && existsDB ) {
                        Console.WriteLine( "Loading scripts..." );
                        setupOk = CreateDBFromScripts( );
                    }
                    else {
                        Console.WriteLine( "Hubo un error al intentar crear la base de datos o el usuario" );
                        setupOk = false;
                    }
                }
            }
            if ( setupOk )
                Console.WriteLine( "[ Database set ]" );
            return setupOk;
        }

        /// <summary>
        /// Loads all scripts to the database
        /// </summary>
        /// <returns>True if database scripts were loaded successfully, false otherwise.</returns>
        static bool CreateDBFromScripts ( ) {
            bool allOk = true;
            Console.Write( "Loading Schema File ({0}) ... ", schemaFile );
            if ( LoadFileToDB( schemaFile ) ) {
                Console.WriteLine( "[ Done ]" );
                foreach ( string f in filesToLoad ) {
                    Console.Write( "Loading File ({0}) ... ", f );
                    allOk = allOk && LoadFileToDB( f );
                    if ( allOk )
                        Console.WriteLine( "[ Done ]" );
                }
            }
            else {
                return false;
            }
            return allOk;
        }

        /// <summary>
        /// Loads a file to the database.
        /// </summary>
        /// <param name="fileName">The name of the file with queries to the database.</param>
        /// <returns>True if the file was loaded successfully, false otherwise.</returns>
        static bool LoadFileToDB ( string fileName ) {
            bool allOk = true;
            try {
                using ( StreamReader re = File.OpenText( Path.Combine( filesDir, fileName ) ) ) {
                    string input = "";
                    string sql = "";
                    string pgConStr = ConfigurationManager.ConnectionStrings [ "ceel" ].ConnectionString;
                    using ( NpgsqlConnection pgDB = new NpgsqlConnection( pgConStr ) ) {
                        try {
                            if ( pgDB.State == ConnectionState.Closed )
                                pgDB.Open( );
                            NpgsqlCommand cmd;
                            string sqlCommand;
                            while ( !re.EndOfStream ) {
                                input = re.ReadLine( );
                                input = input.Trim( );
                                if ( ( input == "" ) || input.StartsWith( "--" ) ) {
                                    continue;
                                }
                                else {
                                    if ( input.Contains( ";" ) ) {
                                        sql += input;
                                        sqlCommand = sql;
                                        cmd = new NpgsqlCommand( sqlCommand, pgDB );
                                        cmd.Prepare( );
                                        allOk = allOk && ( cmd.ExecuteNonQuery( ) < 0 );
                                        sql = "";
                                    }
                                    else {
                                        sql += input;
                                    }
                                }
                            }
                        }
                        catch ( Exception e ) {
                            Console.WriteLine( "Hubo un error al determinar la existencia de la base de datos y el usuario: " + e.Message );
                            allOk = false;
                        }
                        finally {
                            if ( pgDB != null )
                                pgDB.Close( );
                        }
                    }
                    re.Close( );
                }
            }
            catch ( Exception e ) {
                Console.WriteLine( "Hubo un error al cargar el esquema de la base de datos: " + e.Message );
                allOk = false;
            }
            return allOk;
        }
    }
}
